/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package resultados;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import static com.sun.org.apache.xerces.internal.xinclude.XIncludeHandler.BUFFER_SIZE;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.activation.MimetypesFileTypeMap;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author Balrog
 */
@WebServlet(name = "BaseDatos", urlPatterns = {"/BaseDatos"})     // specify urlPattern for servlet
@MultipartConfig   
public class basedatos extends HttpServlet {
    MysqlDataSource datasource;
    Connection conexion;
    java.sql.Statement st;
    String estado="Ok";
    int tamaño1=0;
    int tamaño2=0;

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    
    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

       datasource=new MysqlDataSource();
        datasource.setUser("root");
        datasource.setPassword("root");
        datasource.setDatabaseName("reclamos2014");
        datasource.setServerName("127.0.0.1");
        try {
            conexion=datasource.getConnection();
            st=conexion.createStatement();
        } catch (SQLException ex) {
            estado=ex.getMessage();
                    }  
        PrintWriter sal= response.getWriter();
       try{
/* Mostrando Resultados */
      
      String Fecha_inicial=request.getParameter("fecha_inicial");
      String Fecha_final=request.getParameter("fecha_final");
      String[] Estaciones = request.getParameterValues("Est");
      String Tipo_Estacion=request.getParameter("tip_est");
      String Tipo_exp=request.getParameter("tip_exp");
String aux=null;
String estacion1=null;
      for (int i=0; i<=Estaciones.length-1;i++){
    aux=Estaciones[i].substring(Estaciones[i].length()-3, Estaciones[i].length());
    if(i==0)
    estacion1="("+aux+"-" ;
 if(i==Estaciones.length-1)
      estacion1=estacion1 +aux+")";
 if(i!=0 && i!=Estaciones.length-1)
     estacion1=estacion1 +aux+"-";
     }
      String namefile= "informeRQ_"+Tipo_exp+"_"+Fecha_inicial+"_"+Fecha_final+"_"+Tipo_Estacion+"_"+estacion1+".csv";
      int flag=0;
      String directorio_expedientes = "C:/GestionATO/ReclamosEquipaje/DatosCSV/Nuevo/";
        String ruta_archivo_expedientes;
        File carpeta_expedientes = new File(directorio_expedientes);
        String[] lista = carpeta_expedientes.list();
        for (int i = 0; i < lista.length; i++) {
            if (namefile.equals(lista[i])){
            flag=1;           
            }
        }
      
    namefile=directorio_expedientes+namefile;  
      
      
      
      
if (flag !=1){      
//request.getPart("fecha_inicial");
      //request.getPart("fecha_final");
      //request.getPart("tip_est");
      //request.getPart("est");
      //request.getPart("tip_exp");
      
      //sal.println(request.getPart("fecha_inicial").getHeaders("content-disposition"));
      String Querys1="SELECT "
              + "'EXPD_NMR'," +
"'EXPD_TPO'," +
"'EXPD_CRT_FCH'," +
"'EXPD_APR_CDG'," +
"'EXPD_LNAR_CDG'," +
"'EXPD_PRD_CDG'," +
//"Tipo_Codigo," +  ? CODIGO LATAM con deficion gestion
//"Descripcion_Codigo,\n" + CODIGO LATAM con deficion gestion
"'EXPD_PRD_APR_CDG'," +
"'EXPD_BAG_NMR'," +
"'EXPD_FCH_CLOSURE'," +
"'EXPD_ASSOC'," +
//"Codigo_Linea,\n" +
"'EXPD_NMB'," +
"'EXPD_APE'," +
"'EXPD_TEL'," +
"'EXPD_DIR'," +
"'EXPD_CST_TOTAL'," +
//"Codigo_Costo,\n" +
"'EXPD_ROUTING'," +
//"Clase,\n" +
//"Descripcion_Clase,\n" +
"'FLEX_ID'," +
"'FLEX_FCH'," +
"'FLEX_LNAR_VUELO'," +
"'FLEX_NMR'," +
"'EXPD_ORI'," +
"'EXPD_DES' "+


"UNION ALL "
              + "SELECT EXPD_NMR," +
"EXPD_TPO," +
"EXPD_CRT_FCH," +
"EXPD_APR_CDG," +
"EXPD_LNAR_CDG," +
"EXPD_PRD_CDG," +
//"Tipo_Codigo," +  CODIGO LATAM con deficion gestion
//"Descripcion_Codigo,\n" +CODIGO LATAM con deficion gestion
"EXPD_PRD_APR_CDG," +
"EXPD_BAG_NMR," +
"EXPD_FCH_CLOSURE," +
"EXPD_ASSOC," +
//"Codigo_Linea,\n" +
"EXPD_NMB," +
"EXPD_APE," +
"EXPD_TEL," +
"EXPD_DIR," +
"EXPD_CST_TOTAL," +
//"Codigo_Costo,\n" +
"EXPD_ROUTING," +
//"Clase,\n" +
//"Descripcion_Clase,\n" +
"FLEX_ID," +
"FLEX_FCH," +
"FLEX_LNAR_VUELO," +
"FLEX_NMR," +
"EXPD_ORI," +
"EXPD_DES " +
//"Negocio3,\n" +
//"Negocio4,\n" +
//"Int_Dom,\n" +copia clasificacion latam
//"Pais_Estacion,\n" + copia clasificacion latam
//"Pais_Estacion_Responsable,\n" + copia clasificacion latam
//"Gestionable, " CODIGO LATAM con deficion gestion
               "FROM tablafinal WHERE FLEX_FCH>='"+Fecha_inicial+"' and FLEX_FCH<='"+Fecha_final+"' and "+printEstaciones(Estaciones, TraductorBD(Tipo_Estacion))+ " and EXPD_TPO='"+Tipo_exp+"'"+
              " INTO OUTFILE '"+namefile+"' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\n' ";
      String Querys2="SELECT EXPD_NMR," +
"EXPD_TPO," +
"EXPD_CRT_FCH," +
"EXPD_APR_CDG," +
"EXPD_LNAR_CDG," +
"EXPD_PRD_CDG," +
//"Tipo_Codigo," +
//"Descripcion_Codigo,\n" +
"EXPD_PRD_APR_CDG," +
"EXPD_BAG_NMR," +
"EXPD_FCH_CLOSURE," +
"EXPD_ASSOC," +
//"Codigo_Linea,\n" +
"EXPD_NMB," +
"EXPD_APE," +
"EXPD_TEL," +
"EXPD_DIR," +
"EXPD_CST_TOTAL," +
//"Codigo_Costo,\n" +
"EXPD_ROUTING," +
//"Clase,\n" +
//"Descripcion_Clase,\n" +
"FLEX_ID," +
"FLEX_FCH," +
"FLEX_LNAR_VUELO," +
"FLEX_NMR," +
"EXPD_ORI," +
"EXPD_DES " +
//"Negocio3,\n" +
//"Negocio4,\n" +
//"Int_Dom,\n" +
//"Pais_Estacion,\n" +
//"Pais_Estacion_Responsable,\n" +
//"Gestionable, "
               "FROM tablafinal WHERE FLEX_FCH>='"+Fecha_inicial+"' and FLEX_FCH<='"+Fecha_final+"' and "+ printEstaciones(Estaciones, TraductorBD(Tipo_Estacion))+" and EXPD_TPO='"+Tipo_exp+"'";
    //   ResultSet rs = st.executeQuery(Querys2);
     ResultSet rs2 = st.executeQuery(Querys1);
     // sal.println("<table border = \"1\" >");
      //sal.println("<tr bgcolor = \" #FFFF00 \"><th>Numero Expediente</th><th>Tipo Expediente</th><th>Fecha Creacion</th><th>Estacion</th><th>Linea Area</th><th>Codigo</th><th>Estacion Responsable</th><th>Numero de Maletas</th><th>Fecha Cierre</th><th>Expediente Asociado</th><th>Nombre</th><th>Apellido</th><th>Telefono</th><th>Direccion</th><th>Costo Total</th><th>Routing</th><th>ID Vuelo</th><th>Fecha Vuelo</th><th>Linea Area Vuelo</th><th>Numero Vuelo</th><th>Origen destino</th><th>Destino</th></tr>");
    
}    
     downloadFile(response,
   namefile,sal);

      
      /* while( rs.next() )
      {
       	sal.println("<tr>");
        sal.println("<td>" + rs.getString("EXPD_NMR")+ "</td>" );
sal.println("<td>" + rs.getString("EXPD_TPO")   + "</td>" );
sal.println("<td>" + rs.getString("EXPD_CRT_FCH") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_APR_CDG") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_LNAR_CDG") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_PRD_CDG") +"</td>" );
//"Tipo_Codigo," +
//"Descripcion_Codigo,\n" +
sal.println("<td>" + rs.getString("EXPD_PRD_APR_CDG") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_BAG_NMR") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_FCH_CLOSURE") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_ASSOC") +"</td>" );
//"Codigo_Linea,\n" +
sal.println("<td>" + rs.getString("EXPD_NMB") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_APE") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_TEL") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_DIR") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_CST_TOTAL") +"</td>" );
//"Codigo_Costo,\n" +
sal.println("<td>" + rs.getString("EXPD_ROUTING") +"</td>" );
//"Clase,\n" +
//"Descripcion_Clase,\n" +
sal.println("<td>" + rs.getString("FLEX_ID") +"</td>" );
sal.println("<td>" + rs.getString("FLEX_FCH") +"</td>" );
sal.println("<td>" + rs.getString("FLEX_LNAR_VUELO") +"</td>" );
sal.println("<td>" + rs.getString("FLEX_NMR") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_ORI") +"</td>" );
sal.println("<td>" + rs.getString("EXPD_DES") +"</td>" );
      	sal.println("</tr>");
      }
      
      
      
      
      sal.println("</table>");
      sal.println("</body></html>");*/
    }
    catch( Exception x ) {
    x.printStackTrace();
    }
    }


    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
   
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
public String TraductorBD(String Palabra){
        String sigla = Palabra;
        switch (sigla) {
            case "Estacion":
                sigla="EXPD_APR_CDG";
                break;
            case "Estacion_Responsable":
                sigla="EXPD_PRD_APR_CDG";
                break;
            default:
                return null;

        }
        
    


return sigla;
}


 public String printEstaciones(String[] estaciones, String estacion){
String estacion1= estacion + " IN ";
String aux=null;
for (int i=0; i<=estaciones.length-1;i++){
    aux=estaciones[i].substring(estaciones[i].length()-3, estaciones[i].length());
    if(i==0)
    estacion1=estacion1 + " (' "+aux + "'";
 if(i==estaciones.length-1)
      estacion1=estacion1 + " , '"+aux+"')";
 if(i!=0 && i!=estaciones.length-1)
     estacion1=estacion1 + " , '"+aux+"'";
     }

    return estacion1;
}
public void downloadFile(HttpServletResponse response, String filePath, PrintWriter out)
  throws ServletException, IOException {
 
 File fileToDownload = new File(filePath);
 FileInputStream fileInputStream = new FileInputStream(fileToDownload);
 
 //ServletOutputStream out = response.getOutputStream();   
 //String mimeType =  new MimetypesFileTypeMap().getContentType(filePath); 
 
// response.setContentType(mimeType); 
 //response.setContentLength(fileInputStream.available());
 response.setHeader("Content-Type", "text/csv");
 response.setHeader( "Content-Disposition", "attachment; filename=\""
   + fileToDownload.getName() + "\"" );
 
 int c;
 while((c=fileInputStream.read()) != -1){
  out.write(c);
 }
 out.flush();
 out.close();
 
    
}
}
